<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Transactions</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="MySQL, Perl, DBI, tutorial, database, programming, development">
<meta name="description" content="In this part of the MySQL Perl tutorial, we work with 
database transactions and the autocommit mode.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Transactions</h1>

<p>
In this chapter, we will work with transactions. First we provide some
basic definitions. Then we present Perl scripts that show, how to work
with transactions in Perl DBI. We will also talk about the autocommit mode,
which is essential to understand when dealing with transactions. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<h2>Definitions</h2>

<p>
A <b>transaction</b> is an atomic unit of database operations against 
the data in one or more databases. The effects of all the SQL 
statements in a transaction can be either all committed 
to the database or all rolled back. In the <b>autocommit mode</b>
the changes are immediately effective. To work with transactions we
either turn the autocommit mode off or start a transaction with the
<code>begin_work()</code> method. The transaction is ended with either
the <code>commit()</code> or <code>rollback()</code> methods. 
</p>

<p>
The MySQL database has different types of storage engines. The most common are the 
MyISAM and the InnoDB engines. There is a trade-off between data security and database speed. 
The MyISAM tables are faster to process and they do not support transactions. 
On the other hand, the InnoDB tables are more safe against the data loss. They support 
transactions. They are slower to process.
</p>

<p>
The database connection is in the
autocommit mode by default. The AutoCommit database handle attribute
is used to set or read the autocommit mode. 
</p>

<p>
When the AutoCommit is on, the call to the <code>begin_work()</code> turns
the AutoCommit off. The <code>commit()</code> and <code>rollback()</code> methods
turn the AutoCommit back on. 
If we turn the AutoCommit attribute off and then later call the <code>begin_work()</code> 
method, we receive an error message, that we are already in a transaction.
</p>

<h2>Examples</h2>

<p>
Now we will have some scripts that work with transactions. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1, AutoCommit => 0 },        
) or die $DBI::errstr;

$dbh->do("DROP TABLE IF EXISTS Friends");
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    Name TEXT) ENGINE=InnoDB");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");

$dbh->disconnect();
</pre>

<p>
We create a Friends table and try to fill it with data. However, as we will see,
the data will not be committed. 
</p>

<pre class="explanation">
{ RaiseError => 1, AutoCommit => 0 }, 
</pre>

<p>
We have set the AutoCommit parameter to 0. Changes are not automatically
committed. And there is no commit statement. So the changes are not
written to the database. 
</p>

<pre class="explanation">
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    Name TEXT) ENGINE=InnoDB");
</pre>

<p>
This is the SQL statement to create a Friends table. We have specified 
the InnoDB engine. Note that since MySQL 5.5 the default engine is InnoDB.
In MyISAM tables, SQL statements are committed after they are executed. 
MyISAM tables do not support transactions.
</p>

<pre>
$ ./noautocommit.pl

mysql> SELECT * FROM Friends;
Empty set (0.00 sec)
</pre>

<p>
The table is created but the data is not inserted into the table. 
</p>

<hr class="btm">

<p>
In the second example we will write the data into the database
with the <code>commit()</code> method. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1, AutoCommit => 0 },        
) or die $DBI::errstr;

$dbh->do("DROP TABLE IF EXISTS Friends");
$dbh->do("CREATE TABLE Friends(Id INTEGER PRIMARY KEY AUTO_INCREMENT, 
    Name TEXT) ENGINE=InnoDB");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Tom')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Jim')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Robert')");
$dbh->do("INSERT INTO Friends(Name) VALUES ('Julian')");

$dbh->commit();

$dbh->disconnect();
</pre>

<p>
When the autocommit mode is turned off, every statement is within a transaction
until we call the <code>commit()</code> method. 
</p>

<pre class="explanation">
$dbh->commit();
</pre>

<p>
All changes are written to the database. 
</p>

<pre>
mysql> SELECT * FROM Friends;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Tom     |
|  2 | Rebecca |
|  3 | Jim     |
|  4 | Robert  |
|  5 | Julian  |
+----+---------+
5 rows in set (0.00 sec)
</pre>

<p>
We verify with the sqlite3 command line tool that the changes were written.
</p>


<hr class="btm">

<p>
When there is an error in the transaction, the transaction is rolled back
an no changes are committed to the database. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1, AutoCommit => 0},
) or die $DBI::errstr;

$dbh->do("UPDATE Friends SET Name='Thomas' WHERE Id=1");
$dbh->do("UPDATE Friend SET Name='Bob' WHERE Id=4");

$dbh->commit();

$dbh->disconnect();
</pre>

<p>
In the code example the autocommit is turned off. There are two 
statements which form a transaction. There is an error in the 
second SQL statement. Therefore the transaction is rolled back.
</p>

<pre class="explanation">
$dbh->do("UPDATE Friend SET Name='Bob' WHERE Id=4");
</pre>

<p>
The name of the table is incorrect. There is no Friend table in
the database. 
</p>

<pre>
$ ./rollingback.pl
DBD::mysql::db do failed: Table 'mydb.Friend' doesn't exist at ./rollingback.pl line 14.
DBD::mysql::db do failed: Table 'mydb.Friend' doesn't exist at ./rollingback.pl line 14.
Issuing rollback() due to DESTROY without explicit disconnect() of
DBD::mysql::db handle dbname=mydb at ./rollingback.pl line 14.
</pre>

<p>
Running the example will display this error message. The transaction is 
rolled back. 
</p>

<pre>
mysql> SELECT * FROM Friends;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Tom     |
|  2 | Rebecca |
|  3 | Jim     |
|  4 | Robert  |
|  5 | Julian  |
+----+---------+
5 rows in set (0.00 sec)
</pre>

<p>
No changes took place in the Friends table, even if the first UPDATE statement
was correct. 
</p>

<hr class="btm">

<p>
As we have already mentioned in the tutorial, the default mode is autocommit. 
In this mode we can start a new transaction with the <code>begin_work()</code>
method and finish it with either <code>commit()</code> or <code>rollback()</code>.
The <code>begin_work()</code> method will turn off the autocommit, the 
<code>commit()</code> and the <code>rollback()</code> methods will turn the autocommit
back on. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1, HandleError=>\&amp;handle_error },
) or die $DBI::errstr;

$dbh->begin_work();

$dbh->do("UPDATE Friends SET Name='Thomas' WHERE Id=1");
$dbh->do("UPDATE Friend SET Name='Bob' WHERE Id=4");

$dbh->commit();

$dbh->do("INSERT INTO Friends(Name) VALUES('Ronald')");

$dbh->disconnect();


sub handle_error {

    my $error = shift;
    print "An error occured in the script\n";
    print "Message: $error\n";
    return 1;
}
</pre>

<p>
Again we have an incorrect second SQL statement. This time we do not
explicitly turn off the autocommit. 
</p>

<pre class="explanation">
{ RaiseError => 1, HandleError=>\&amp;handle_error },  
</pre>

<p>
We will delegate error handling to the <code>handle_error()</code> subroutine.
</p>

<pre class="explanation">
$dbh->begin_work();
</pre>

<p>
With the <code>begin_work()</code> method, we start a new transaction. 
The autocommit is turned off. 
</p>

<pre class="explanation">
$dbh->do("UPDATE Friends SET Name='Thomas' WHERE Id=1");
$dbh->do("UPDATE Friend SET Name='Bob' WHERE Id=4");
</pre>

<p>
These two statements form a transaction. The second one is incorrect.
</p>

<pre class="explanation">
sub handle_error {

    my $error = shift;
    print "An error occured in the script\n";
    print "Message: $error\n";
    return 1;
}
</pre>

<p>
This subroutine is called when we encounter an error. We print an error
message. Note that the script is not exited. 
</p>

<pre class="explanation">
$dbh->do("INSERT INTO Friends(Name) VALUES('Ronald')");
</pre>

<p>
The transaction was rolled back and we did not exit the script. It
continues. After the rollback, the autocommit was turned back on. 
A new row was added to the Friends table. 
</p>

<pre>
$ ./rollingback2.pl
An error occured in the script
Message: DBD::mysql::db do failed: Table 'mydb.Friend' doesn't exist
</pre>

<p>
We can see our custom error message from the handle_error() subroutine.  
</p>

<pre>
mysql> SELECT * FROM Friends;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Thomas  |
|  2 | Rebecca |
|  3 | Jim     |
|  4 | Robert  |
|  5 | Julian  |
|  6 | Ronald  |
+----+---------+
6 rows in set (0.00 sec)
</pre>

<p>
A new friend was inserted to the table.  
</p>


<p>
In this part of the MySQL Perl tutorial, we have worked with transactions.
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified May 6, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

